Combats Scrolls - Ivan Shu

Закрыть и забыть
declare
/* PRIVATE FUNCTIONS START */
type vchar_tab is table OF varchar2(32000);
type sql_rec is record (ssql VARCHAR2(4000));
type sql_cur IS ref cursor return sql_rec;

procedure write_sql_line(
in_str in varchar2
)
is
c_start_point pls_integer := 1;
c_step pls_integer := 150;
c_length pls_integer;
c_token varchar2(150 char);
begin
c_length := c_step;

if length(in_str) < c_step then
dbms_output.put_line(in_str);
return;
end if;

c_token := substr(in_str, c_start_point, c_step);

while length(c_token) = c_step
loop
c_length := instr(c_token,',',-1);
c_token := substr(in_str, c_start_point, c_length);

dbms_output.put_line(c_token);

c_start_point := c_start_point + c_length;

c_token := substr(in_str, c_start_point, c_step);
if length(c_token) < c_step then
dbms_output.put_line(c_token);
exit;
end if;
end loop;

end write_sql_line;

FUNCTION get_primary_key (
in_schema IN VARCHAR2
, in_table_name IN VARCHAR2
, pk_col_nr OUT NUMBER)
RETURN VARCHAR2
IS
l_pk_col VARCHAR2(127 char);
CURSOR c1 (a_owner VARCHAR2, a_table VARCHAR2) IS SELECT ic.column_name
FROM sys.all_constraints co, sys.all_ind_columns ic
WHERE co.constraint_name = ic.index_name
AND co.owner = a_owner
AND co.table_name = a_table
AND co.constraint_type = 'P'
AND ic.index_owner = co.owner
AND ic.table_name = co.table_name;
BEGIN
pk_col_nr := 0;
FOR cr IN c1(upper(in_schema),upper(in_table_name))
LOOP
l_pk_col := cr.column_name;
pk_col_nr := pk_col_nr + 1;
END LOOP;
RETURN l_pk_col;
END get_primary_key;

PROCEDURE export_data(
in_schema IN VARCHAR2
, in_table IN VARCHAR2
, in_where_clause IN VARCHAR2)
IS
l_cur sql_cur;
c NUMBER;
dummy NUMBER;
col_cnt INTEGER;
col_num INTEGER;
ssql VARCHAR2(32000 char);
sql_ins VARCHAR2(4000 char);
sql_val VARCHAR2(32000 char);
sql_sel VARCHAR2(4000 char);
sql_select VARCHAR2(4000 char);
rec_tab dbms_sql.desc_tab;
l_tab vchar_tab;
str_vals vchar_tab;
pk_col VARCHAR2(32 char);
pk_cnt NUMBER;
str_val VARCHAR2(4000 char);
colcnt2 NUMBER;
coltypes vchar_tab;
b_pk_numeric NUMBER := 0;
li NUMBER;
sql_order VARCHAR2(500 char);
BEGIN
c := dbms_sql.open_cursor;

sql_sel := 'SELECT * FROM ' || in_schema || '.' || in_table;

pk_col := get_primary_key(in_schema,in_table,pk_cnt);

IF pk_cnt = 1 THEN
sql_order := 'ORDER BY ' || pk_col;
sql_sel := sql_sel || ' ' || sql_order;
ELSIF pk_cnt > 1 THEN
sql_order := 'ORDER BY ';
li := 0;
FOR cr IN (SELECT ic.column_name FROM sys.all_constraints co, sys.all_ind_columns ic
WHERE co.constraint_name = ic.index_name
AND co.owner = in_schema
AND co.table_name = in_table
AND co.constraint_type = 'P'
AND ic.index_owner = co.owner
AND ic.table_name = co.table_name
ORDER BY column_position)
LOOP
IF li > 0 THEN
sql_order := sql_order || ',';
END IF;
sql_order := sql_order || cr.COLUMN_NAME;
li := li + 1;
END LOOP;
sql_sel := sql_sel || ' ' || sql_order;
END IF;

dbms_sql.parse(c, sql_sel, dbms_sql.native);
dummy := dbms_sql.execute(c);
dbms_sql.describe_columns(c, col_cnt, rec_tab);

l_tab := vchar_tab();
str_vals := vchar_tab();
coltypes := vchar_tab();
sql_ins := 'INSERT INTO ' || in_schema || '.' || in_table || ' (';
sql_select := ' SELECT ';
colcnt2 := 0;

FOR col_num IN 1..col_cnt
LOOP
colcnt2 := colcnt2 + 1;
IF col_num > 1 THEN sql_ins := sql_ins || ','; sql_select := sql_select || ','; END IF;
sql_ins := sql_ins || rec_tab(col_num).col_name;

coltypes.EXTEND;
coltypes(colcnt2) := rec_tab(col_num).col_type;
CASE rec_tab(col_num).col_type
WHEN 2 THEN --number
IF (rec_tab(col_num).col_scale = 0 OR rec_tab(col_num).col_scale = -127) THEN
sql_select := sql_select || rec_tab(col_num).col_name;
ELSE
sql_select := sql_select || 'trim(to_char(' || rec_tab(col_num).col_name ||',''999999990.99''))';
END IF;
IF rec_tab(col_num).col_name = pk_col THEN
b_pk_numeric := 1;
END IF;
WHEN 1 THEN --varchar2
sql_select := sql_select || rec_tab(col_num).col_name;
WHEN 12 THEN --date
sql_select := sql_select || 'to_char(' || rec_tab(col_num).col_name || ',''yyyy-mm-dd HH24:mi:ss'')';
WHEN 180 THEN --timestamp
sql_select := sql_select || 'to_char(' || rec_tab(col_num).col_name || ',''yyyy-mm-dd HH24:mi:ss.ff'')';
ELSE -- treat like varchar2
sql_select := sql_select || rec_tab(col_num).col_name;
END CASE;
END LOOP;

sql_ins := sql_ins || ') VALUES (';
sql_select := sql_select || ' FROM ' || in_schema || '.' || in_table;

IF in_where_clause is not null THEN
sql_select := sql_select || ' ' || in_where_clause;
END IF;

IF sql_order IS NOT NULL THEN
sql_select := sql_select || ' ' ||sql_order;
END IF;

dbms_sql.close_cursor(c);
c := dbms_sql.open_cursor;
dbms_sql.parse(c, sql_select, dbms_sql.native);
dummy := dbms_sql.execute(c);

FOR col_num IN 1..colcnt2
LOOP
str_vals.EXTEND;
str_vals(col_num) := '';
CASE to_number(coltypes(col_num))
WHEN 2 THEN --number
DBMS_SQL.DEFINE_COLUMN(c, col_num, str_vals(col_num),22);
WHEN 1 THEN --varchar2
DBMS_SQL.DEFINE_COLUMN(c, col_num, str_vals(col_num),4000);
WHEN 12 THEN --date
DBMS_SQL.DEFINE_COLUMN(c, col_num, str_vals(col_num),20);
WHEN 180 THEN --timestamp
DBMS_SQL.DEFINE_COLUMN(c, col_num, str_vals(col_num),26);
ELSE -- treat like varchar2
DBMS_SQL.DEFINE_COLUMN(c, col_num, str_vals(col_num),4000);
END CASE;
END LOOP;

sql_val := '';

LOOP
IF DBMS_SQL.FETCH_ROWS(c)>0 THEN
sql_val := ' ';
FOR col_num IN 1..colcnt2
LOOP
IF col_num > 1 THEN
sql_val := sql_val || ',';
END IF;
CASE to_number(coltypes(col_num))
WHEN 2 THEN
DBMS_SQL.COLUMN_VALUE(c, col_num, str_vals(col_num));
sql_val := sql_val || nvl(str_vals(col_num),'NULL');
WHEN 1 THEN
DBMS_SQL.COLUMN_VALUE(c, col_num, str_vals(col_num));
IF str_vals(col_num) is NULL THEN
sql_val := sql_val || 'NULL' ;
ELSE
str_val := replace(str_vals(col_num),chr(10),''' || chr(10) || ''');
str_val := replace(str_val,chr(13),''' || ''');
str_val := replace(str_val,'''','''''');
sql_val := sql_val || '''' || str_val || '''';
END IF;
WHEN 12 THEN
DBMS_SQL.COLUMN_VALUE(c, col_num, str_vals(col_num));
IF str_vals(col_num) is NULL THEN
sql_val := sql_val || 'NULL' ;
ELSE
sql_val := sql_val || 'to_date(''' || str_vals(col_num) || ''',''yyyy-mm-dd hh24:mi:ss'')';
END IF;
WHEN 180 THEN
DBMS_SQL.COLUMN_VALUE(c, col_num, str_vals(col_num));
IF str_vals(col_num) is NULL THEN
sql_val := sql_val || 'NULL' ;
ELSE
sql_val := sql_val || 'to_timestamp(''' || str_vals(col_num) || ''',''yyyy-mm-dd hh24:mi:ss.ff'')';
END IF;
ELSE
DBMS_SQL.COLUMN_VALUE(c, col_num, str_vals(col_num));
IF str_vals(col_num) is NULL THEN
sql_val := sql_val || 'NULL' ;
ELSE
str_val := replace(str_vals(col_num),chr(10),''' || chr(10) || ''');
str_val := replace(str_val,chr(13),''' || ''');
str_val := replace(str_val,'''','''''');
sql_val := sql_val || '''' || str_val || '''';
END IF;
END CASE;
END LOOP;
ssql := sql_ins || sql_val || ');';
l_tab.EXTEND;
l_tab(l_tab.COUNT) := ssql;
ELSE
EXIT;
END IF;
END LOOP;

dbms_sql.close_cursor(c);

IF l_tab.count = 0 THEN
RETURN;
END IF;

FOR i IN l_tab.first .. l_tab.last
LOOP
write_sql_line(l_tab(i));
END LOOP;
END export_data;

/* PRIVATE FUNCTIONS END */
begin
dbms_output.enable(1000000);
-- put your logics here like this:
export_data('<your schema name>','<you table namer>','<your where clause for given table>');
end;

login:			password: